package com.xbongbong.pro.dataset.helper;

import com.alibaba.fastjson.JSONArray;
import com.xbongbong.paas.bi.pojo.DataSetNodeFieldPojo;
import com.xbongbong.paas.constant.PaasConstant;
import com.xbongbong.paas.constant.XbbConstant;
import com.xbongbong.paas.toolbox.exception.XbbException;
import com.xbongbong.paas.toolbox.util.CollectionsUtil;
import com.xbongbong.paas.toolbox.wrap.BaseDTO;
import com.xbongbong.pro.bi.commons.filter.SrFilterHandler;
import com.xbongbong.pro.bi.commons.filter.SrSelectItemHandler;
import com.xbongbong.pro.bi.commons.helper.CollectionHelper;
import com.xbongbong.pro.bi.commons.pojo.ParamPojo;
import com.xbongbong.pro.bi.commons.sql.SqlBuilderHandler;
import com.xbongbong.pro.statistic.pojo.ChartFieldAttrPojo;
import com.xbongbong.pro.statistics.help.DescribeTableHelper;
import com.xbongbong.saas.constant.StringConstant;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;
import net.sf.jsqlparser.statement.select.SelectItem;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author gzt
 * @version 1.0
 * @Description -sql解析核心类
 * @datetime - 2022/5/25-10:32
 * @since v1.0
 */
@Slf4j
@Component
public class JsqlParseHelper {
    @Resource
    private SrFilterHandler sRFilterHandler;
    @Resource
    private SrSelectItemHandler sRSelectItemHandler;
    @Resource
    private DescribeTableHelper describeTableHelper;
    private static final Pattern SELECT_PATTERN = Pattern.compile("SELECT(.*?)FROM");
    public static String parseSql(String pre,String sql) {
        try {
            String selectItems = null;
            List<SelectItem> selectItemList = getSelectItems(sql);
            if (selectItemList.size()>0){
                if (Objects.nonNull(((SelectExpressionItem)selectItemList.get(0)).getAlias())){
                    selectItems = selectItemList.stream().map(item->pre +"."+ ((SelectExpressionItem)item).getAlias().getName())
                            .collect(Collectors.joining(","));
                }else {
                    selectItems = regexGetItem(sql);
                    if (Objects.nonNull(selectItems)) {
                        selectItems = Arrays.stream(selectItems.trim().split(XbbConstant.SQL_COMMA)).map(item -> pre +XbbConstant.POINT+ item).collect(Collectors.joining(XbbConstant.SQL_COMMA));
                    }
                }
            }
            return selectItems;
        } catch (Exception e) {
            log.error("sql解析异常",e);
        }
        return null;
    }


    public static String parseSql(String pre,String sql,List<String> exList) {
        try {
            String joiner = null;
            List<SelectItem> selectItems = getSelectItems(sql);
            if (CollectionHelper.isNotEmptyList(selectItems) ) {
                SelectItem selectItem = selectItems.get(0);
                if (Objects.nonNull(((SelectExpressionItem) selectItem).getAlias())){
                    joiner = selectItems.stream().map(item->((SelectExpressionItem) item).getAlias().getName()).filter(l1->exList.stream().noneMatch(l2->l2.equals(l1)))
                            .map(item -> pre +XbbConstant.POINT+ item).collect(Collectors.joining(XbbConstant.SQL_COMMA));
                }else{
                    joiner = selectItems.stream().map(item->((Column)(((SelectExpressionItem) item).getExpression().getASTNode().jjtGetValue())).getColumnName()).filter(l1->exList.stream().noneMatch(l2->l2.equals(l1)))
                            .map(item -> pre +XbbConstant.POINT+ item).collect(Collectors.joining(XbbConstant.SQL_COMMA));
                }
            }
            return joiner;
        } catch (JSQLParserException e) {
            log.error("sql解析异常",e);
        } catch (Exception e) {
            log.error("sql解析异常",e);
        }
        return null;
    }

    private static List<SelectItem> getSelectItems(String sql) throws JSQLParserException {
        sql = regexGetItemWithMatch(sql)+ XbbConstant.SPACE +"A";
        Statement statement = CCJSqlParserUtil.parse(sql);
        Select selectStatement = (Select) statement;
        List<SelectItem> selectItems = ((PlainSelect) selectStatement.getSelectBody()).getSelectItems();
        return selectItems;
    }

    /**
     * @author  gzt
     * @Description append过滤条件
     * @Date 13:38 2022/10/12
     * @param nodeArray
     * @param sqlBuilder
     * @param businessType
     * @param formId
     * @param corpid
     * @param filterMap
     * @return com.xbongbong.pro.bi.commons.sql.SqlBuilderHandler
     * @throws XbbException
     **/

    public SqlBuilderHandler appendFilter(JSONArray nodeArray, SqlBuilderHandler sqlBuilder,Integer businessType,Long formId,
                                          String corpid,Map<String, StringBuffer> filterMap,boolean isMultiJoinUnion) throws XbbException {
        if (nodeArray != null && nodeArray.size()>0) {
            Map<Long, Boolean> sourcesMap = null;
            if (!JsonObjectHelper.getIsEmptyJsonArray(nodeArray)){
                return sqlBuilder;
            }
            // 筛选条件处理常用和自定义动态
            describeTableHelper.searchCondition(nodeArray,corpid,10000L);
            if (isMultiJoinUnion){
                sRFilterHandler.appendFilter(nodeArray,sqlBuilder,businessType,filterMap,isMultiJoinUnion);
            }else {
                sRFilterHandler.parseFilterOrSearchCondition(corpid, sqlBuilder, nodeArray, false,
                        sourcesMap, false,businessType,filterMap);
            }
        }
        return sqlBuilder;
    }


    public  SqlBuilderHandler initSqlBuilder(SqlBuilderHandler sqlBuilder,ParamPojo paramPojo,HashMap<String, Set<String>> listHashMapPairValue,
                                             String tableName,String fields){
        String itemStr = sRSelectItemHandler.getItemStr(listHashMapPairValue, tableName);
        String initSql = sRFilterHandler.initSqlString(paramPojo.getCorpid(), paramPojo.getFormId(),
                paramPojo.getBusinessType(), null, null).end().getSql();
        sqlBuilder.select(fields).from(XbbConstant.SPACE+XbbConstant.L_P+XbbConstant.SELECT+XbbConstant.SPACE).append(itemStr)
                .append(initSql);
        return sqlBuilder;
    }

    public SqlBuilderHandler generatItems(String tableName,SqlBuilderHandler sqlBuilder, Set<String> fieldCheckSet,
                                          Map<Long, String> formIdTableMap,
                                          BaseDTO baseDTO, ParamPojo paramPojo,List<ChartFieldAttrPojo> explainEntityList,
                                          Map<String,StringBuffer>  filterMap) throws XbbException {
        if (CollectionsUtil.isEmpty(explainEntityList)) {
            explainEntityList = sRSelectItemHandler.getExplainEntityList(baseDTO.getCorpid(), fieldCheckSet, paramPojo);
        }
        Pair<List<String>, HashMap<String, Set<String>>> listHashMapPair = sRSelectItemHandler.displayAttrAssemble(explainEntityList, null, null,
                null, null, null, true, null, formIdTableMap);
        HashMap<String, Set<String>> listHashMapPairValue = listHashMapPair.getValue();

        String selectItems = listHashMapPair.getKey().stream().collect(Collectors.joining(StringConstant.COMMA));
        this.initSqlBuilder(sqlBuilder, paramPojo, listHashMapPairValue, tableName, selectItems);
        SqlBuilderHandler teamJoinSqlHandler = SqlBuilderHandler.start();
        sRSelectItemHandler.appendTeamJoin(teamJoinSqlHandler,selectItems,baseDTO,listHashMapPair,paramPojo,filterMap);
        return teamJoinSqlHandler;
    }

    public static void main(String[] args) {
        String sql = "SELECT text_1,creatorId_1,addTime_1,text_2 FROM (SELECT text_1 as text_1,creator_id as creatorId_1,add_time as addTime_1,text_68 as text_2 FROM (SELECT id,tb_saas_refund.add_time AS add_time,JSONExtractRaw(tb_saas_refund.`data`,'array_4') AS array_4,tb_saas_refund_user.team_main AS myTeam_1,tb_saas_refund.creator_id AS creator_id,if(JSONHas(tb_saas_refund.`data`,'date_1'),JSONExtractInt(tb_saas_refund.`data`,'date_1'),null) AS date_1,JSONExtractFloat(tb_saas_refund.`data`,'num_1') AS num_1,JSONExtractFloat(tb_saas_refund.`data`,'num_2') AS num_2,JSONExtractFloat(tb_saas_refund.`data`,'num_4') AS num_4,JSONExtractFloat(tb_saas_refund.`data`,'num_5') AS num_5,tb_saas_refund_user.team_main AS myTeamMain_1,tb_saas_refund.serial_no AS serial_no,JSONExtractArrayRaw(tb_saas_refund.`data`,'subForm_1') AS subForm_1,JSONExtractArrayRaw(tb_saas_refund.`data`,'subForm_2') AS subForm_2,JSONExtract(tb_saas_refund.`data`,'text_1','String') AS text_1,JSONExtract(tb_saas_refund.`data`,'text_13','String') AS text_13,JSONExtract(tb_saas_refund.`data`,'text_14','String') AS text_14,JSONExtract(tb_saas_refund.`data`,'text_16','String') AS text_16,JSONExtract(tb_saas_refund.`data`,'text_2','String') AS text_2,JSONExtract(tb_saas_refund.`data`,'text_3','String') AS text_3,JSONExtract(tb_saas_refund.`data`,'text_5','String') AS text_5,JSONExtract(tb_saas_refund.`data`,'text_68','String') AS text_68,tb_saas_refund.tupdate_time AS update_time FROM (SELECT id,argMax(creator_id,update_time) as creator_id,argMax(serial_no,update_time) as serial_no,argMax(add_time,update_time) as add_time,argMax(update_time,update_time) as tupdate_time,argMax(data,update_time) as data,argMax(del,update_time) as del FROM tb_saas_refund WHERE corpid = '1' AND form_id = 6374 AND flow_status in [0, 2] group by id having del = 0 ) AS tb_saas_refund LEFT SEMI　JOIN ( select * from ( select data_id,groupArray(if(is_main == 0,user_id,null)) AS team_co ,groupArray(if(is_main == 1,user_id,null)) AS team_main from (SELECT argMax(user_id ,update_time) as user_id ,argMax(user_name ,update_time) as user_name ,argMax(data_id ,update_time) as data_id ,argMax(is_main ,update_time) as is_main ,argMax(del,update_time) as del from tb_saas_refund_user prewhere corpid = '1' group by id having del = 0 ) GROUP BY data_id ) where 1 ) AS tb_saas_refund_user ON tb_saas_refund.id = tb_saas_refund_user.data_id ORDER BY tb_saas_refund.id desc) UNION ALL SELECT text_1 as text_1,creator_id as creatorId_1,add_time as addTime_1,text_4 as text_2 FROM (SELECT id,tb_saas_customer_communicate.add_time AS add_time,JSONExtractRaw(tb_saas_customer_communicate.`data`, 'address_1') AS address_1,JSONExtractRaw(tb_saas_customer_communicate.`data`,'array_6') AS array_6,JSONExtractRaw(tb_saas_customer_communicate.`data`,'array_7') AS array_7,tb_saas_customer_communicate.creator_id AS creator_id,if(JSONHas(tb_saas_customer_communicate.`data`,'date_1'),JSONExtractInt(tb_saas_customer_communicate.`data`,'date_1'),null) AS date_1,JSONExtractArrayRaw(tb_saas_customer_communicate.`data`,'subForm_2') AS subForm_2,JSONExtract(tb_saas_customer_communicate.`data`,'text_1','String') AS text_1,JSONExtractInt(tb_saas_customer_communicate.`data`,'text_27') AS text_27,JSONExtract(tb_saas_customer_communicate.`data`,'text_3','String') AS text_3,JSONExtract(tb_saas_customer_communicate.`data`,'text_4','String') AS text_4,tb_saas_customer_communicate.tupdate_time AS update_time FROM (SELECT id,argMax(creator_id,update_time) as creator_id,argMax(add_time,update_time) as add_time,argMax(update_time,update_time) as tupdate_time,argMax(data,update_time) as data,argMax(del,update_time) as del FROM tb_saas_customer_communicate WHERE corpid = '1' AND form_id = 5095 AND flow_status in [0, 2] group by id having del = 0 AND JSONExtract(`data`,'text_1','String') = '27072296' ) AS tb_saas_customer_communicate ORDER BY tb_saas_customer_communicate.id desc)) AS AA";
        regexGetItem(sql);
    }

    private static String regexGetItem(String sql) {
        Matcher m = SELECT_PATTERN.matcher(sql);
        if (m.find()) {
            return m.group(1);
        }
        return null;
    }

    private static String regexGetItemWithMatch(String sql) {
        Matcher m = SELECT_PATTERN.matcher(sql);
        if (m.find()) {
            return m.group(0);
        }
        return null;
    }


    public void reMoveRedundant(List<DataSetNodeFieldPojo> unionNodeFields, List<DataSetNodeFieldPojo> hideField) {
        Set<String> attrCoo = new HashSet<>(PaasConstant.DEFAULT_INITIAL_CAPACITY);
        for (DataSetNodeFieldPojo dataSetNodeFieldPojo : unionNodeFields) {
            attrCoo.add(dataSetNodeFieldPojo.getAttr());
        }
        hideField.removeIf(dataSetNodeFieldPojo -> attrCoo.contains(dataSetNodeFieldPojo.getAttr()));
    }
}
